package cn.pugle.ormtest.jdbctemplate;

import cn.pugle.ormtest.mybatis.bean.CustomerBean;
import com.fasterxml.jackson.core.JsonProcessingException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import static cn.pugle.ormtest.common.JSON.SNAKE_MAPPER;

/**
 * spring提供的jdbcTemplate.
 * 查询的表是resources/mybatis那个.
 *
 * @author tzp
 * @date 2017/12/21
 */
@Component
public class JdbcTemplateDemo {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void retrieve() throws JsonProcessingException {
        //retrieve resultSet
        CustomerBean oneCustomer = jdbcTemplate.query("SELECT id, name FROM st_customer1 WHERE id = ?",
                rs -> {
                    if (rs.next()) {
                        return rsToCustomer(rs);
                    } else {
                        return null;
                    }
                }, 1);
        System.out.println("oneCustomer: " + SNAKE_MAPPER.writeValueAsString(oneCustomer));

        //retrieve for object. 所有query带class作为参数的, 只是将一列转为那个class, 而不是将所有列映射过去
        CustomerBean oneCustomer1 = jdbcTemplate.queryForObject("SELECT id, name FROM st_customer1 WHERE id = ?",
                new Object[]{1}, (rs, rowNum) -> rsToCustomer(rs));
        System.out.println("oneCustomer1: " + SNAKE_MAPPER.writeValueAsString(oneCustomer1));

        //retrieve for map. only one row too.
        Map<String, Object> map = jdbcTemplate.queryForMap("SELECT id, name FROM st_customer1 WHERE id = 1");
        System.out.println("map: " + SNAKE_MAPPER.writeValueAsString(map));

        List<Map<String, Object>> customers = jdbcTemplate.queryForList("SELECT id, name FROM st_customer1");
        System.out.println("list: " + SNAKE_MAPPER.writeValueAsString(customers));
    }

    private CustomerBean rsToCustomer(ResultSet rs) throws SQLException {
        CustomerBean customer = new CustomerBean();
        customer.setId(rs.getLong(1));
        customer.setName(rs.getString(2));
        return customer;
    }
}
